package ee.maoinvest.backend.model.dao.postgresqlimplementations;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import ee.maoinvest.GeneralConstants;
import ee.maoinvest.backend.model.dao.ElectricityContractDAO;
import ee.maoinvest.backend.model.data.ElectricityContract;
import ee.maoinvest.backend.model.data.ElectricityContractType;
import ee.maoinvest.backend.model.data.ElectricityMeter;
import ee.maoinvest.backend.database.ConnectionManager;

public class ElectricityContractDAOPostgresqlImpl implements ElectricityContractDAO
{
	Logger logger = Logger.getLogger(ElectricityContractDAOPostgresqlImpl.class);
	Connection dbConnection;
	String sql;
	PreparedStatement preparedStatement;
	Statement statement;
	ResultSet resultSet;
	private final int WARNING_NO_ROWS_AFFECTED = 0;
	private int rowsAffected;
	
	public ElectricityContractDAOPostgresqlImpl(Connection dbConnection)
	{
		this.dbConnection = dbConnection;
	}
	
//-----------------START OF BASIC CRUD-----------------------------------
	@Override
	public int insertElectricityContract(
			ElectricityContract electricityContract) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "INSERT INTO Electricity_contracts " + 
				"(electricityContractName, electricityContractTypeFK, dateInserted, dateUpdated, " +
				"ampereBasedFeePerA, transmissionFeeDayRatePerkWh, transmissionFeeNightRatePerkWh, reactivePowerFeePerkvarh, " +  
				"currency, isInUse, isWrittenOff, dateOfWriteOff, isDefaultContract)" + 
				"VALUES " + 
				"(?, ?, ?, ?, " +
				"?, ?, ?, ?, " +  
				"?, ?, ?, ?, ?);";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			
			preparedStatement.setString		(1, electricityContract.getElectricityContractName());
			preparedStatement.setInt		(2, electricityContract.getElectricityContractTypeFK());
			preparedStatement.setDate		(3, electricityContract.getDateInserted());
			preparedStatement.setObject		(4, electricityContract.getDateUpdated(), Types.DATE);
			preparedStatement.setBigDecimal	(5, electricityContract.getAmpereBasedFeePerA());
			preparedStatement.setBigDecimal	(6, electricityContract.getTransmissionFeeDayRatePerkWh());
			preparedStatement.setBigDecimal	(7, electricityContract.getTransmissionFeeNightRatePerkWh());
			preparedStatement.setBigDecimal	(8, electricityContract.getReactivePowerFeePerkvarh());
			preparedStatement.setString		(9, electricityContract.getCurrency());
			preparedStatement.setBoolean	(10, electricityContract.getIsInUse());
			preparedStatement.setBoolean	(11, electricityContract.getIsWrittenOff());
			preparedStatement.setObject		(12, electricityContract.getDateOfWriteOff(), Types.DATE);
			preparedStatement.setBoolean	(13, electricityContract.getIsDefaultContract());
			
			logger.info(preparedStatement.toString());
			rowsAffected = preparedStatement.executeUpdate();
			
			if (rowsAffected == 1)
			{
				resultSet = preparedStatement.getGeneratedKeys();
				resultSet.next(); //answer consist only 1 row, so move the cursor just once to start to process the result
				int newId = resultSet.getInt(1); // 1, because identifiers are held in the first column 
				electricityContract.setElectricityContractID(newId);
				return rowsAffected;
			}
			else
			{
				System.out.println("No rows affected!");
				logger.info("Unsuccessful insertion to a database: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			return WARNING_NO_ROWS_AFFECTED;
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}

	@Override
	public ElectricityContract getElectricityContractByID(int electricityContractID) 
	{
		resultSet = null;
		ElectricityContract electricityContractRetrieved = null;
		sql = "SELECT * FROM Electricity_contracts WHERE electricityContractID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
						sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY
					);
			//filling the placeholders 
			preparedStatement.setInt(1, electricityContractID);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of a received data set (so it can start to read and process data)
			resultSet.beforeFirst();
			
			if (resultSet.next())
			{
				electricityContractRetrieved = new ElectricityContract();
				electricityContractRetrieved.setElectricityContractID(resultSet.getInt("electricityContractID"));
				electricityContractRetrieved.setElectricityContractName(resultSet.getString("electricityContractName"));
				electricityContractRetrieved.setElectricityContractTypeFK(resultSet.getInt("electricityContractTypeFK"));
				electricityContractRetrieved.setDateInserted(resultSet.getDate("dateInserted"));
				electricityContractRetrieved.setDateUpdated((Date) resultSet.getObject("dateUpdated"));
				electricityContractRetrieved.setAmpereBasedFeePerA(resultSet.getBigDecimal("ampereBasedFeePerA"));
				electricityContractRetrieved.setTransmissionFeeDayRatePerkWh(resultSet.getBigDecimal("transmissionFeeDayRatePerkWh"));
				electricityContractRetrieved.setTransmissionFeeNightRatePerkWh(resultSet.getBigDecimal("transmissionFeeNightRatePerkWh"));
				electricityContractRetrieved.setReactivePowerFeePerkvarh(resultSet.getBigDecimal("reactivePowerFeePerkvarh"));
				electricityContractRetrieved.setCurrency(resultSet.getString("currency"));
				electricityContractRetrieved.setIsInUse(resultSet.getBoolean("isInUse"));
				electricityContractRetrieved.setIsWrittenOff(resultSet.getBoolean("isWrittenOff"));
				electricityContractRetrieved.setDateOfWriteOff((Date) resultSet.getObject("dateOfWriteOff"));
				electricityContractRetrieved.setIsDefaultContract(resultSet.getBoolean("isDefaultContract"));
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return electricityContractRetrieved;
	}

	@Override
	public int updateElectricityContract(
			ElectricityContract electricityContract) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "UPDATE Electricity_contracts " + 
				"SET electricityContractName = ?, electricityContractTypeFK = ?, dateInserted = ?, dateUpdated = ?, " +
				"ampereBasedFeePerA = ?, transmissionFeeDayRatePerkWh = ?, transmissionFeeNightRatePerkWh = ?, reactivePowerFeePerkvarh = ?, " + 
				"currency = ?, isInUse = ?, isWrittenOff = ?, dateOfWriteOff = ?, isDefaultContract = ? " + 
				"WHERE electricityContractID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			
			preparedStatement.setString		(1, electricityContract.getElectricityContractName());
			preparedStatement.setInt		(2, electricityContract.getElectricityContractTypeFK());
			preparedStatement.setDate		(3, electricityContract.getDateInserted());
			preparedStatement.setObject		(4, electricityContract.getDateUpdated(), Types.DATE);
			preparedStatement.setBigDecimal	(5, electricityContract.getAmpereBasedFeePerA());
			preparedStatement.setBigDecimal	(6, electricityContract.getTransmissionFeeDayRatePerkWh());
			preparedStatement.setBigDecimal	(7, electricityContract.getTransmissionFeeNightRatePerkWh());
			preparedStatement.setBigDecimal	(8, electricityContract.getReactivePowerFeePerkvarh());
			preparedStatement.setString		(9, electricityContract.getCurrency());
			preparedStatement.setBoolean	(10, electricityContract.getIsInUse());
			preparedStatement.setBoolean	(11, electricityContract.getIsWrittenOff());
			preparedStatement.setObject		(12, electricityContract.getDateOfWriteOff(), Types.DATE);
			preparedStatement.setBoolean	(13, electricityContract.getIsDefaultContract());
			preparedStatement.setInt		(14, electricityContract.getElectricityContractID());
			
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Update sucessful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.error("Update was unsuccesful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
			return WARNING_NO_ROWS_AFFECTED;		
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}

	@Override
	public int deleteElectricityContract(int electricityContractID) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "DELETE FROM Electricity_contracts WHERE electricityContractID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			preparedStatement.setInt(1, electricityContractID);
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Deletion successful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.info("Deletion unsuccessful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch(SQLException e) 
		{
			e.printStackTrace();
			return WARNING_NO_ROWS_AFFECTED;
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}
	
//-----------------END OF BASIC CRUD-----------------------------------
	@Override
	public List<ElectricityContract> getAllElectricityContractsThatAreNotWrittenOff() 
	{
		List<ElectricityContract> listOfElectricityContracts = new ArrayList<ElectricityContract>();
		ElectricityContract electricityContractRetrieved = null;
		sql = "SELECT * FROM Electricity_contracts WHERE isWrittenOff = false;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				electricityContractRetrieved = fillElectricityContractObjectWithDataRetrievedFromDB();
				
				listOfElectricityContracts.add(electricityContractRetrieved);
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return listOfElectricityContracts;
	}

	@Override
	public ElectricityContract getDefaultElectricityContract() 
	{
		resultSet = null;
		ElectricityContract electricityContractRetrieved = null;
		sql = "SELECT * FROM Electricity_contracts WHERE isDefaultContract = true AND isWrittenOff = false;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
						sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY
					);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of a received data set (so it can start to read and process data)
			resultSet.beforeFirst();
			
			if (resultSet.next())
			{
				electricityContractRetrieved = fillElectricityContractObjectWithDataRetrievedFromDB();
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return electricityContractRetrieved;
	}
	
	@Override
	public List<ElectricityContract> getAllElectricityContractsForOuterTerritoryMetersThatAreNotWrittenOff() 
	{
		List<ElectricityContract> listOfElectricityContracts = new ArrayList<ElectricityContract>();
		ElectricityContract electricityContractRetrieved = null;
		sql = "SELECT * FROM Electricity_contracts WHERE electricityContractTypeFK = ? AND isWrittenOff = false AND isDefaultContract = false;";

		try 
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			preparedStatement.setInt(1, GeneralConstants.ELECTRICITY_CONTRACT_TYPE_ID_FOR_OUTER_TERRITORY_METERS_IN_DB);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				electricityContractRetrieved = fillElectricityContractObjectWithDataRetrievedFromDB();
				
				listOfElectricityContracts.add(electricityContractRetrieved);
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return listOfElectricityContracts;
	}
	
	@Override
	public List<ElectricityContractType> getAllElectricityContractTypes() 
	{
		List<ElectricityContractType> listOfElectricityContractTypes = new ArrayList<ElectricityContractType>();
		ElectricityContractType electricityContractTypeRetrieved = null;
		sql = "SELECT * FROM Electricity_contract_types;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				electricityContractTypeRetrieved = new ElectricityContractType();
				electricityContractTypeRetrieved.setElectricityContractTypeID(resultSet.getInt("electricityContractTypeID"));
				electricityContractTypeRetrieved.setTypeName(resultSet.getString("typeName"));
				
				listOfElectricityContractTypes.add(electricityContractTypeRetrieved);
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return listOfElectricityContractTypes;
	}
	
	@Override
	public int setIsElectricityContractInUse(int electricityContractID,
			boolean isInUse) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "UPDATE Electricity_contracts " + 
				"SET isInUse = ? " +
				"WHERE electricityContractID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			
			preparedStatement.setBoolean(1, isInUse);
			preparedStatement.setInt	(2, electricityContractID);
			
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Update sucessful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.error("Update was unsuccesful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
			return WARNING_NO_ROWS_AFFECTED;		
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}
	
	@Override
	public int endSpecificElectricityMeterElectricityContractPeriod(
			ElectricityMeter electricityMeter) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "UPDATE Electricity_meter_and_its_electricity_contract_periods " +
				"SET contractPeriodEndDate = ?, " +
				"electricityMeterDaytimeReadingAtPeriodEndDate = ?, electricityMeterNightTimeReadingAtPeriodEndDate = ?, electricityMeterReactivePowerReadingAtPeriodEndDate = ? " +
				"WHERE electricityMeterFK = ? AND contractPeriodEndDate IS NULL";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			
			preparedStatement.setDate		(1, electricityMeter.getDateUpdated());
			preparedStatement.setBigDecimal	(2, electricityMeter.getDaytimeReadingWhileRemoving());
			preparedStatement.setBigDecimal	(3, electricityMeter.getNightTimeReadingWhileRemoving());
			preparedStatement.setBigDecimal	(4, electricityMeter.getReactivePowerReadingWhileRemoving());
			preparedStatement.setInt 		(5, electricityMeter.getElectricityMeterID());
			
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Update sucessful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.error("Update was unsuccesful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
			return WARNING_NO_ROWS_AFFECTED;		
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}
	
	@Override
	public List<ElectricityContract> getAllElectricityContractsForLocalTerritoryElectricityMetersThatAreNotWrittenOff() 
	{
		List<ElectricityContract> listOfElectricityContracts = new ArrayList<ElectricityContract>();
		ElectricityContract electricityContractRetrieved = null;
		sql = "SELECT * FROM Electricity_contracts WHERE electricityContractTypeFK = ? " +
				"AND electricityContractID != ? AND isWrittenOff = false;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			preparedStatement.setInt(1, GeneralConstants.ELECTRICITY_CONTRACT_TYPE_ID_FOR_LOCAL_TERRITORY_METERS_IN_DB);
			preparedStatement.setInt(2, GeneralConstants.DEFAULT_ELECTRICITY_CONTRACT_VAIKIMISI_PAKETT_ID_IN_DATABASE);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				electricityContractRetrieved = fillElectricityContractObjectWithDataRetrievedFromDB();
				
				listOfElectricityContracts.add(electricityContractRetrieved);
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return listOfElectricityContracts;
	}
	
	//HELPER METHODS
	private ElectricityContract fillElectricityContractObjectWithDataRetrievedFromDB()
	{
		ElectricityContract electricityContractRetrieved = null;
		
		try 
		{
			electricityContractRetrieved = new ElectricityContract();
			
			electricityContractRetrieved.setElectricityContractID(resultSet.getInt("electricityContractID"));
			electricityContractRetrieved.setElectricityContractName(resultSet.getString("electricityContractName"));
			electricityContractRetrieved.setElectricityContractTypeFK(resultSet.getInt("electricityContractTypeFK"));
			electricityContractRetrieved.setDateInserted(resultSet.getDate("dateInserted"));
			electricityContractRetrieved.setDateUpdated((Date) resultSet.getObject("dateUpdated"));
			electricityContractRetrieved.setAmpereBasedFeePerA(resultSet.getBigDecimal("ampereBasedFeePerA"));
			electricityContractRetrieved.setTransmissionFeeDayRatePerkWh(resultSet.getBigDecimal("transmissionFeeDayRatePerkWh"));
			electricityContractRetrieved.setTransmissionFeeNightRatePerkWh(resultSet.getBigDecimal("transmissionFeeNightRatePerkWh"));
			electricityContractRetrieved.setReactivePowerFeePerkvarh(resultSet.getBigDecimal("reactivePowerFeePerkvarh"));
			electricityContractRetrieved.setCurrency(resultSet.getString("currency"));
			electricityContractRetrieved.setIsInUse(resultSet.getBoolean("isInUse"));
			electricityContractRetrieved.setIsWrittenOff(resultSet.getBoolean("isWrittenOff"));
			electricityContractRetrieved.setDateOfWriteOff((Date) resultSet.getObject("dateOfWriteOff"));
			electricityContractRetrieved.setIsDefaultContract(resultSet.getBoolean("isDefaultContract"));
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return electricityContractRetrieved;
	}
}
